// 检查通知相关的数据库表
const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'aide',
  user: 'postgres',
  password: 'unidt@123',
});

async function checkNotificationTables() {
  const client = await pool.connect();
  
  try {
    console.log('🔍 检查通知相关数据库表...\n');
    
    // 检查所有表
    const tablesResult = await client.query(`
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_schema = 'public' 
      ORDER BY table_name
    `);
    
    console.log('📋 现有数据库表:');
    const existingTables = tablesResult.rows.map(row => row.table_name);
    existingTables.forEach(table => console.log(`  - ${table}`));
    
    // 检查通知相关的表
    const notificationTables = [
      'notification_settings',
      'notification_history', 
      'notification_queue'
    ];
    
    console.log('\n🔔 通知相关表检查:');
    for (const tableName of notificationTables) {
      const exists = existingTables.includes(tableName);
      console.log(`  ${exists ? '✅' : '❌'} ${tableName}`);
      
      if (exists) {
        // 检查表结构
        const columnsResult = await client.query(`
          SELECT column_name, data_type, column_default, is_nullable
          FROM information_schema.columns 
          WHERE table_name = $1 AND table_schema = 'public'
          ORDER BY ordinal_position
        `, [tableName]);
        
        console.log(`     字段 (${columnsResult.rows.length}个):`);
        columnsResult.rows.forEach(col => {
          console.log(`       - ${col.column_name} (${col.data_type})`);
        });
        
        // 检查数据
        const countResult = await client.query(`SELECT COUNT(*) FROM ${tableName}`);
        console.log(`     数据量: ${countResult.rows[0].count} 条`);
      }
    }
    
    // 检查是否有用户的通知设置
    if (existingTables.includes('notification_settings')) {
      console.log('\n👤 检查用户通知设置...');
      
      const userSettingsResult = await client.query(`
        SELECT ns.*, u.email
        FROM notification_settings ns
        JOIN users u ON ns.user_id = u.id
      `);
      
      console.log(`找到 ${userSettingsResult.rows.length} 个用户的通知设置:`);
      userSettingsResult.rows.forEach(setting => {
        console.log(`  - ${setting.email}: task_reminders=${setting.task_reminders}`);
      });
      
      if (userSettingsResult.rows.length === 0) {
        console.log('⚠️ 没有找到任何用户的通知设置，这可能是问题所在！');
        
        // 查找管理员用户
        const adminResult = await client.query(`
          SELECT id, email FROM users WHERE email = 'admin@aide.com'
        `);
        
        if (adminResult.rows.length > 0) {
          const adminId = adminResult.rows[0].id;
          console.log(`\n🔧 为管理员用户创建默认通知设置...`);
          
          await client.query(`
            INSERT INTO notification_settings (
              user_id, task_reminders, daily_summary, weekly_review, 
              overdue_alerts, reminder_time, sound_enabled, vibration_enabled
            ) VALUES ($1, true, true, true, true, '09:00', true, true)
            ON CONFLICT (user_id) DO NOTHING
          `, [adminId]);
          
          console.log('✅ 默认通知设置已创建');
        }
      }
    }
    
  } catch (error) {
    console.error('❌ 检查过程中发生错误:', error.message);
    console.error('详细错误:', error);
  } finally {
    client.release();
    await pool.end();
  }
}

checkNotificationTables();
